package com.hbnu.dao.impl;

import com.hbnu.dao.UserDao;
import com.hbnu.pojo.DormBuild;
import com.hbnu.pojo.User;
import com.hbnu.util.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author chendikai
 * @date 2024-11-06 上午 10:38
 */
public class UserDaoImpl implements UserDao {
    @Override
    public User findUserByStuCodeAndPassword(String stuCode, String password) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();

            String sql = "select * from tb_user where stu_code = ? and password = ? and disabled = 0";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, stuCode);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();   // 实体类对象

                // 将查询出来的数据封装到User对象中
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setStuCode(resultSet.getString("stu_code"));
                user.setDormCode(resultSet.getString("dorm_code"));
                user.setSex(resultSet.getString("sex"));
                user.setTel(resultSet.getString("tel"));
                user.setDormBuildId(resultSet.getInt("dormBuildId"));
                user.setRoleId(resultSet.getInt("role_id"));
                user.setCreateUserId(resultSet.getInt("create_user_id"));
                user.setDisabled(resultSet.getInt("disabled"));

                return user;   // 返回查出来的对象
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public List<User> findManager(String searchType, String keyword) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<User> userList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();

            String temp = "";
            if (null != searchType && null != keyword && !"".equals(keyword)) {
                if ("name".equals(searchType)) {
                    temp = " and name like '%" + keyword + "%'";
                }
                if ("sex".equals(searchType)) {
                    temp = " and sex like '%" + keyword + "%'";
                }
                if ("tel".equals(searchType)) {
                    temp = " and tel like '%" + keyword + "%'";
                }
            }

            String sql = "select * from tb_user where role_id = 1";

            if (!"".equals(temp)) {
                sql += temp;
            }
            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();

                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setStuCode(resultSet.getString("stu_code"));
                user.setSex(resultSet.getString("sex"));
                user.setTel(resultSet.getString("tel"));
                user.setRoleId(resultSet.getInt("role_id"));
                user.setCreateUserId(resultSet.getInt("create_user_id"));
                user.setDisabled(resultSet.getInt("disabled"));

                userList.add(user);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public User findById(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();

            String sql = "select * from tb_user where id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();

                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setStuCode(resultSet.getString("stu_code"));
                user.setDormCode(resultSet.getString("dorm_code"));
                user.setSex(resultSet.getString("sex"));
                user.setTel(resultSet.getString("tel"));
                user.setDormBuildId(resultSet.getInt("dormbuildId"));
                user.setRoleId(resultSet.getInt("role_id"));
                user.setCreateUserId(resultSet.getInt("create_user_id"));
                user.setDormCode(resultSet.getString("dorm_code"));

                return user;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public Integer saveManager(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            String sql = "INSERT INTO tb_user(name,password,stu_code,sex,tel,role_id,create_user_id,disabled) VALUES(?,?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getStuCode());
            preparedStatement.setString(4, user.getSex());
            preparedStatement.setString(5, user.getTel());
            preparedStatement.setInt(6, user.getRoleId());
            preparedStatement.setInt(7, user.getCreateUserId());
            preparedStatement.setInt(8, user.getDisabled());

            preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();
            resultSet.next();
            return resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace(); // 更好的做法是记录日志或重新抛出异常
        }
        return null;
    }

    @Override
    public void updateManager(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();

            String sql = "update tb_user set name=?,password=?,sex=?,tel=?,disabled=? where id=?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getSex());
            preparedStatement.setString(4, user.getTel());
            preparedStatement.setInt(5, user.getDisabled());
            preparedStatement.setInt(6, user.getId());

            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public String findManagerStuCodeMax() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();

            String sql = "select stu_code from tb_user where id=(select max(id) from tb_user)";

            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String stu_code = resultSet.getString("stu_code");
                return "00" + (Integer.parseInt(stu_code) + 1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> findStudent(String sql) {
        // ① 获取连接（数据库地址 用户名 密码）
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        ResultSet rs = null;
        try {
            connection = DBUtils.getConnection();
// ③ 获取集装箱或者说是车
            preparedStatement = connection.prepareStatement(sql);
// ④执行SQL,获取执行后的结果,查询的结果封装在ResultSet
            rs = preparedStatement.executeQuery();
// 因为查询出来的结果包括表头信息，所以要指针下移一行，看是否有查询出来的数据
// 如有数据，就进入循环体，封装该行数据
            List<User> users = new ArrayList<User>();
            while (rs.next()) {
                User user = new User();
// 每一行的数据封装在一个实体bean中，根据字段名获取字段值，注意该字段是什么类型，就get什么类型
                user.setId(rs.getInt("id"));
                user.setCreateUserId(rs.getInt("create_user_id"));
                user.setDisabled(rs.getInt("disabled"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setRoleId(rs.getInt("role_id"));
                user.setSex(rs.getString("sex"));
                user.setStuCode(rs.getString("stu_code"));
                user.setTel(rs.getString("tel"));
                user.setDormCode(rs.getString("dorm_code"));
                user.setDormBuildId(rs.getInt("dormBuildId"));

                DormBuild build = new DormBuild();
                build.setId(rs.getInt("dormBuildId"));
                build.setName(rs.getString("buildName"));
                build.setRemark(rs.getString("remark"));
                user.setDormBuild(build);
                users.add(user);
            }
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public Integer findTotalNum(String sql) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();

            preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();

            resultSet.next();
            return resultSet.getInt(1);
        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public User findByStuCode(String stuCode) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        ResultSet rs = null;
        try {
            connection = DBUtils.getConnection();

            String sql = "select * from tb_user where stu_code=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,stuCode);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                User user = new User();

                user.setId(rs.getInt("id"));
                user.setCreateUserId(rs.getInt("create_user_id"));
                user.setDisabled(rs.getInt("disabled"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setRoleId(rs.getInt("role_id"));
                user.setSex(rs.getString("sex"));
                user.setStuCode(rs.getString("stu_code"));
                user.setTel(rs.getString("tel"));
                user.setDormCode(rs.getString("dorm_code"));
                user.setDormBuildId(rs.getInt("dormBuildId"));

                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public void updateStudent(User user1) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtils.getConnection();

            String sql = "UPDATE tb_user SET name = ?,password=?,sex=?,tel=?,disabled= ?,stu_code=?,dorm_code=?,dormBuildId=? WHERE id=?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user1.getName());
            preparedStatement.setString(2, user1.getPassword());
            preparedStatement.setString(3, user1.getSex());
            preparedStatement.setString(4, user1.getTel());
            preparedStatement.setInt(5, user1.getDisabled());
            preparedStatement.setString(6, user1.getStuCode());
            preparedStatement.setString(7, user1.getDormCode());
            preparedStatement.setInt(8, user1.getDormBuildId());
            preparedStatement.setInt(9, user1.getId());

            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(null, preparedStatement, connection);
        }

    }

    @Override
    public void saveStudent(User user2) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection=DBUtils.getConnection();

            String sql="insert into tb_user(name,password,stu_code,dorm_code,sex,tel,dormBuildId,role_id,create_user_id)  VALUES(?,?,?,?,?,?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1, user2.getName());
            preparedStatement.setString(2, user2.getPassword());
            preparedStatement.setString(3, user2.getStuCode());
            preparedStatement.setString(4, user2.getDormCode());
            preparedStatement.setString(5, user2.getSex());
            preparedStatement.setString(6, user2.getTel());
            preparedStatement.setInt(7, user2.getDormBuildId());
            preparedStatement.setInt(8,user2.getRoleId());
            preparedStatement.setInt(9,user2.getCreateUserId());

            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(resultSet,preparedStatement,connection);
        }

    }

    @Override
    public User findByUserIdAndManager(String string) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        ResultSet rs = null;
        try {
            connection = DBUtils.getConnection();

            preparedStatement = connection.prepareStatement(string);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                User user = new User();

                user.setId(rs.getInt("id"));
                user.setCreateUserId(rs.getInt("create_user_id"));
                user.setDisabled(rs.getInt("disabled"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setRoleId(rs.getInt("role_id"));
                user.setSex(rs.getString("sex"));
                user.setStuCode(rs.getString("stu_code"));
                user.setTel(rs.getString("tel"));
                user.setDormCode(rs.getString("dorm_code"));
                user.setDormBuildId(rs.getInt("dormBuildId"));

                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public User findStuCodeAndManager(String string) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        ResultSet rs = null;
        try {
            connection = DBUtils.getConnection();

            preparedStatement = connection.prepareStatement(string);

            rs = preparedStatement.executeQuery();

            while (rs.next()) {
                User user = new User();

                user.setId(rs.getInt("id"));
                user.setCreateUserId(rs.getInt("create_user_id"));
                user.setDisabled(rs.getInt("disabled"));
                user.setDormBuildId(rs.getInt("dormBuildId"));
                user.setDormCode(rs.getString("dorm_code"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setRoleId(rs.getInt("role_id"));
                user.setSex(rs.getString("sex"));
                user.setStuCode(rs.getString("stu_code"));
                user.setTel(rs.getString("tel"));

                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(rs, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public void updatePassword(User user) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            connection=DBUtils.getConnection();

            String sql="update tb_user set password=? where id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getPassword());
            preparedStatement.setInt(2,user.getId());

            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(null,preparedStatement,connection);
        }
    }

}
